package com.fowo.api.common.mybatis.helper;

import com.fowo.api.common.meta.MetaDataObjects;
import com.fowo.api.common.mybatis.interceptor.RolePermissionInterceptor;
import com.fowo.api.common.util.ContextHolder;
import com.fowo.api.sys.rd2.RoleData2FilterService;
import com.fowo.api.user.model.JwtUserInfo;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;
import lombok.experimental.Accessors;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.CastExpression;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.create.table.ColDataType;
import org.apache.ibatis.executor.Executor;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 用于在 Mybatis Xml 中使用的 SQL 帮助组件
 * @author yl_ls
 */
@Slf4j
@Component
public class MybatisSqlHelper {
    private final PageSearchSqlDialect sqlDialect;
    @Resource
    private DataSource dataSource;

    static final int RANGE_ALL = 1;
    static final int RANGE_CUSTOM = 2;
    static final int RANGE_DEP = 3;
    static final int RANGE_DEP_DEEP = 4;
    static final int RANGE_SELF = 5;

    static final int CACHE_TIME = 60000;
    private final Map<Long, UserRangesCache> userRangesCacheMap = new HashMap<>();

    public MybatisSqlHelper() {
        sqlDialect = PageSearchHelper.sqlDialectByDbType(MetaDataObjects.getObjects().get(0).getDbType());
    }

    private void preQueryUserRanges(JwtUserInfo jwtUserInfo, RolePermissionMetadata metadata) {
        long now = new Date().getTime();
        UserRangesCache userRangesCache = userRangesCacheMap.get(jwtUserInfo.getUserId());
        if (userRangesCache != null) {
            if (now < userRangesCache.createTime + CACHE_TIME) {
                metadata.setRanges(userRangesCache.getRanges());
                return;
            } else {
                userRangesCacheMap.remove(jwtUserInfo.getUserId());
            }
        }
        try (final Connection connection = dataSource.getConnection()) {
            final PreparedStatement preparedStatement = connection.prepareStatement("SELECT distinct SRP.range FROM sys_role_permission SRP\n" +
                    "JOIN sys_user_role SUR ON SUR.del_flag = 0 and SUR.role_id = SRP.role_id\n" +
                    "WHERE SRP.del_flag = 0 and SUR.user_id = ?\n" +
                    "ORDER BY SRP.range");
            preparedStatement.setLong(1, jwtUserInfo.getUserId());
            final ResultSet resultSet = preparedStatement.executeQuery();
            List<Integer> ranges = new ArrayList<>();
            while (resultSet.next()) {
                ranges.add(resultSet.getInt(1));
            }
            resultSet.close();
            preparedStatement.close();

            metadata.setRanges(ranges);

            userRangesCache = new UserRangesCache();
            userRangesCache.setCreateTime(now);
            userRangesCache.setRanges(ranges);
            userRangesCacheMap.put(jwtUserInfo.getUserId(), userRangesCache);
        }
        catch (SQLException e) {
            log.error("处理数据范围权限预查时发生错误", e);
        }
    }

    @SuppressWarnings("unused")
    public String dataPermissionWhereStatements(String objectName, String alias, String prefix) {
        JwtUserInfo jwtUserInfo = JwtUserInfo.fromHeader();
        if (jwtUserInfo == null || jwtUserInfo.getUserId() == null) {
            return "";
        }

        RolePermissionMetadata metadata = new RolePermissionMetadata();
        metadata.setCurrentUserId(jwtUserInfo.getUserId());
        preQueryUserRanges(jwtUserInfo, metadata);
        metadata.setOwnerUserIdColumnName(String.format("%s.%s", alias, "create_user"));

        List<String> conditionList = new ArrayList<>();
        if (metadata.isEmptyRange() || metadata.getMinRange() == RANGE_SELF) {
            return String.format("%s%s = %d", prefix, metadata.ownerUserIdColumnName, metadata.currentUserId);
        }
        else if (metadata.getMinRange() == RANGE_ALL) {
            return "";
        }
        if (metadata.hasRange(RANGE_CUSTOM)) {
            String sqlCustomIn = String.format("%s IN (\n" +
                    "                \tSELECT SUD.user_id FROM sys_user_department SUD\n" +
                    "                \tINNER JOIN sys_role_permission_departments as SRPD ON SRPD.department_id = SUD.department_id\n" +
                    "                \tinner join sys_role_permission as SRP on SRP.id = role_permission_id AND SRP.del_flag = 0 AND SRP.range = 2\n" +
                    "                \tinner join sys_user_role as SUR on SUR.role_id = SRP.role_id\n" +
                    "                \twhere SUR.del_flag = 0 AND SUR.user_id = %d\n" +
                    "                )", metadata.ownerUserIdColumnName, metadata.currentUserId);
            conditionList.add(sqlCustomIn);
        }
        if (metadata.hasRange(RANGE_DEP_DEEP)) {
            // JSON_CONTAINS(SD.parents, CAST(SUD.department_id AS CHAR))
            String jsonContains = sqlDialect.jsonArrayFieldInExpression(
                    new Column(new Table().withAlias(new Alias("SD")), "parents"),
                    new CastExpression().withLeftExpression(
                            new Column("department_id").withTable(new Table().withAlias(new Alias("SUD")))
                    ).withType(new ColDataType("CHAR"))
            ).toString();
            String sqlDepDeep = String.format("%s IN (SELECT DISTINCT user_id\n" +
                    "FROM sys_user_department\n" +
                    "WHERE department_id IN (\n" +
                    "SELECT DISTINCT id\n" +
                    "FROM sys_department SD\n" +
                    "JOIN sys_user_department SUD ON SUD.user_id = %d AND (\n" +
                    "\tSD.id = SUD.department_id OR %s\t\n" +
                    ")\n" +
                    // 不应该因为删除部门造成数据不可用
                    // "WHERE del_flag = 0\n" +
                    "))", metadata.ownerUserIdColumnName, metadata.currentUserId, jsonContains);
            conditionList.add(sqlDepDeep);
        }
        else if (metadata.hasRange(RANGE_DEP)) {
            String sqlDep = String.format("%s IN (SELECT DISTINCT DSUD.user_id\n" +
                    "FROM sys_user_department DSUD\n" +
                    "JOIN sys_user_department CSUD ON CSUD.department_id = DSUD.department_id AND CSUD.user_id = %d)", metadata.ownerUserIdColumnName, metadata.getCurrentUserId());
            conditionList.add(sqlDep);
        }
        if (metadata.hasRange(RANGE_SELF)) {
            conditionList.add(String.format("%s = %d", metadata.ownerUserIdColumnName, metadata.currentUserId));
        }

        if (conditionList.isEmpty()) {
            return "";
        }
        return prefix + "(" + String.join(" OR ", conditionList) + ")";
    }

    /**
     * 提供基于部门的数据权限过滤语句
     * @param deptIdColumnName 部门字段完整名称
     * @param prefix 前缀
     * @return
     */
    @SuppressWarnings("unused")
    public String dataPermissionDeptWhereStatements(String deptIdColumnName, String prefix) {
        JwtUserInfo jwtUserInfo = JwtUserInfo.fromHeader();
        if (jwtUserInfo == null || jwtUserInfo.getUserId() == null) {
            return "";
        }

        RolePermissionMetadata metadata = new RolePermissionMetadata();
        metadata.setCurrentUserId(jwtUserInfo.getUserId());
        preQueryUserRanges(jwtUserInfo, metadata);
        metadata.setOwnerDepartmentColumnName(deptIdColumnName);

        List<String> conditionList = new ArrayList<>();
        if (metadata.isEmptyRange() || metadata.getMinRange() == RANGE_SELF) {
            return String.format("%s%s = %d", prefix, metadata.ownerUserIdColumnName, metadata.currentUserId);
        }
        else if (metadata.getMinRange() == RANGE_ALL) {
            return "";
        }
        if (metadata.hasRange(RANGE_CUSTOM)) {
            String sqlCustomIn = String.format("%s IN (\n" +
                    "                \tSELECT SRPD.department_id\n" +
                    "                \tFROM sys_role_permission_departments as SRPD" +
                    "                \tinner join sys_role_permission as SRP on SRP.id = role_permission_id AND SRP.del_flag = 0 AND SRP.range = 2\n" +
                    "                \tinner join sys_user_role as SUR on SUR.role_id = SRP.role_id\n" +
                    "                \twhere SUR.del_flag = 0 AND SUR.user_id = %d\n" +
                    "                )", metadata.ownerDepartmentColumnName, metadata.currentUserId);
            conditionList.add(sqlCustomIn);
        }
        if (metadata.hasRange(RANGE_DEP_DEEP)) {
            // JSON_CONTAINS(SD.parents, CAST(SUD.department_id AS CHAR))
            String jsonContains = sqlDialect.jsonArrayFieldInExpression(
                    new Column(new Table().withAlias(new Alias("SD")), "parents"),
                    new CastExpression().withLeftExpression(
                            new Column("department_id").withTable(new Table().withAlias(new Alias("SUD")))
                    ).withType(new ColDataType("CHAR"))
            ).toString();
            String sqlDepDeep = String.format("%s IN (\n" +
                    "SELECT DISTINCT id\n" +
                    "FROM sys_department SD\n" +
                    "JOIN sys_user_department SUD ON SUD.user_id = %d AND (\n" +
                    "\tSD.id = SUD.department_id OR %s\t\n" +
                    ")\n" +
                    // 不应该因为删除部门造成数据不可用
                    // "WHERE del_flag = 0\n" +
                    ")", metadata.ownerDepartmentColumnName, metadata.currentUserId, jsonContains);
            conditionList.add(sqlDepDeep);
        }
        else if (metadata.hasRange(RANGE_DEP)) {
            String sqlDep = String.format("%s IN (SELECT CSUD.id " +
                    "FROM sys_user_department CSUD WHERE CSUD.user_id = %d)", metadata.ownerDepartmentColumnName, metadata.getCurrentUserId());
            conditionList.add(sqlDep);
        }
//        if (metadata.hasRange(RANGE_SELF)) {
//            conditionList.add(String.format("%s = %d", metadata.ownerUserIdColumnName, metadata.currentUserId));
//        }

        if (conditionList.isEmpty()) {
            return "";
        }
        return prefix + "(" + String.join(" OR ", conditionList) + ")";
    }

    @SuppressWarnings("unused")
    public String roleData2(String objectName, String userColumn, String alias) {
        RoleData2FilterService roleData2FilterService = ContextHolder.getApplicationContext().getBean(RoleData2FilterService.class);
        return roleData2FilterService.appendInnerJoin(objectName, userColumn, alias);
    }

    @Getter
    @Setter
    static class UserRangesCache implements Serializable {
        private long createTime;
        private List<Integer> ranges;
    }

    /**
     * 保存特定 mappedId 对应的元数据信息
     */
    @Getter
    @Setter
    @Accessors(chain = true)
    static class RolePermissionMetadata implements Serializable {
        /**
         * 当前用户编号
         */
        private Long currentUserId;
        /**
         * 当前用户数据权限范围（从小到大）
         */
        private List<Integer> ranges;
        /**
         * 实体类
         */
        private Class<?> entityClass;
        /**
         * 数据所有者列名
         */
        private String ownerUserIdColumnName;
        /**
         * 数据所有部门列名
         */
        private String ownerDepartmentColumnName;

        public Integer getMinRange() {
            if (!isEmptyRange()) {
                return ranges.get(0);
            }
            return null;
        }

        public boolean isEmptyRange() {
            return ranges == null || ranges.isEmpty();
        }

        public boolean hasRange(int range) {
            if (!isEmptyRange()) {
                return ranges.contains(range);
            }
            return false;
        }

        public boolean hasOwnerDepartmentColumn() {
            return StringUtils.hasText(ownerDepartmentColumnName);
        }
    }
}
